package ming.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author hjj
 * @date 2020年3月19日
 */
public final class JdbcUtil {

    static final String jdbcUrl = "jdbc:mysql://127.0.0.1:3306/web_framwork?useUnicode=true"
            + "&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false";
    static final String userName = "root";
    static final String password = "root";

    private static Connection getConn() {
        return getConn0();
    }

    private static void releaseConn(Connection conn) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static Connection getConn0() {
        try {
            //com.mysql.cj.jdbc.Driver com.mysql.jdbc.Driver
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(jdbcUrl, userName, password);
            return conn;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("未找到JDBC驱动", e);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("获取数据库连接失败", e);
        }
    }

    /**
     * 执行sql，根据字段顺序返回表记录,每条记录包装成List<Object>，不包含字段名称
     *
     * @param sql
     * @param param
     * @return
     * @author LAN
     * @date 2020年3月25日
     */
    public static List<List<Object>> selectSqlToList(String sql, Object... param) {
        sql = sql.trim();
        if (!"select".equals(sql.substring(0, 6).toLowerCase())) {
            throw new RuntimeException("查询必须以select开始");
        }
        Connection conn = getConn();
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pstm.setObject(i + 1, param[i]);
                }
            }
            ResultSet rs = pstm.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<List<Object>> list = new ArrayList();
            while (rs.next()) {
                List<Object> row = new ArrayList();
                for (int i = 1; i <= columnCount; i++) {
                    Object cell = rs.getObject(i);
                    row.add(cell);
                }
                list.add(row);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询数据库失败", e);
        } finally {
            releaseConn(conn);
        }
    }

    /**
     * 执行sql，根据字段名称包装成Map<String,Object>,键是字段名称
     *
     * @param sql
     * @param param
     * @return
     * @author LAN
     * @date 2020年3月25日
     */
    public static List<Map<String, Object>> selectSqlToMap(String sql, Object... param) {
        sql = sql.trim();
        if (!"select".equals(sql.substring(0, 6).toLowerCase())) {
            throw new RuntimeException("查询必须以select开始");
        }
        Connection conn = getConn();
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pstm.setObject(i + 1, param[i]);
                }
            }
            ResultSet rs = pstm.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            List<Map<String, Object>> list = new ArrayList();
            while (rs.next()) {
                Map<String, Object> row = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object cell = rs.getObject(i);
                    row.put(columnName, cell);
                }
                list.add(row);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询数据库失败", e);
        } finally {
            releaseConn(conn);
        }
    }

    /**
     * 增删改操作
     *
     * @param sql
     * @param param
     * @return
     * @author LAN
     * @date 2020年3月25日
     */
    public static int insertOrUpdate(String sql, Object... param) {
        Connection conn = getConn();
        try {
            PreparedStatement pstm = conn.prepareStatement(sql);
            if (param != null) {
                for (int i = 0; i < param.length; i++) {
                    pstm.setObject(i + 1, param[i]);
                }
            }
            boolean execute = pstm.execute();
            int updateCount = pstm.getUpdateCount();
            return updateCount;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("操作数据库失败", e);
        } finally {
            releaseConn(conn);
        }
    }

}